package com.bzxgc.sgwer.common.page;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 使用MySQL数据库limit关键字实现分页
 */
public class JdbcSqlStudentDaoImpl implements StudentDao {

    @Override
    public Pager<Student> findStrudent(Student student, int pageNum, int pageSize) {
        Pager<Student> result = null;
        // 存放查询参数
        List<Object> paramList = new ArrayList<>();

        String stuName = student.getStuName();
        int gender = student.getGender();

        StringBuilder sql = new StringBuilder("select * from t_student where 1=1");
        StringBuilder countSql = new StringBuilder("select count(id) as totalRecord from t_student where 1=1");

        if (stuName != null && !stuName.equals("")) {
            sql.append(" and stu_name like ?");
            countSql.append(" and stu_name like ?");
            paramList.add("%" + stuName + "%");
        }
        if (gender == Constant.GENDER_MALE && gender == Constant.GENDER_FEMALE) {
            sql.append(" and gender=?");
            countSql.append(" and gender=?");
            paramList.add(gender);
        }

        // 起始索引
        int fromIndex = pageSize * (pageNum - 1);

        // 使用limit关键字，实现分页
        sql.append(" limit " + fromIndex + "," + pageSize);

        List<Student> studentList = new ArrayList<>();
        JdbcUtil jdbcUtil = null;
        try {
            jdbcUtil = new JdbcUtil();
            // 获取数据库连接
            jdbcUtil.getConnection();

            // 获取总记录数
            List<Map<String, Object>> countResult = jdbcUtil.findResult(countSql.toString(), paramList);
            Map<String, Object> countMap = countResult.get(0);
            int totalRecord = ((Number) countMap.get("totalRecord")).intValue();

            // 获取查询的学生记录
            List<Map<String, Object>> studentResult = jdbcUtil.findResult(sql.toString(), paramList);
            if (studentResult != null) {
                for (Map<String, Object> map : studentResult) {
                    Student s = new Student(map);
                    studentList.add(s);
                }
            }

            // 获取总页数
            int totalPage =  totalRecord / pageSize;
            if (totalRecord % pageSize != 0) {
                totalPage++;
            }
            result = new Pager<>(pageSize, pageNum, totalPage, totalRecord, studentList);
        } catch (SQLException e) {
            throw new RuntimeException("查询所有数据异常！", e);
        } finally {
            if (jdbcUtil != null) {
                // 一定要释放资源
                jdbcUtil.releaseConn();
            }
        }
        return result;
    }
}
